﻿using System;
using System.Data.SqlTypes;
using System.Net;
using System.IO;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean FtpUpload(SqlString ftpServer, SqlString ftpUser, SqlString ftpPassword, SqlString ftpPath, SqlString ftpFilename, SqlString localPath, SqlString localFile)
    {
        try
        {
            //create ftp object
            string ftpFullPath = "ftp://" + ftpServer.Value + ftpPath.Value + ftpFilename.Value;
            FtpWebRequest ftp = (FtpWebRequest)FtpWebRequest.Create(ftpFullPath);

            //  ftp.Credentials = new NetworkCredential("anonymous", "anonymous");
            //userid and password for the ftp server
            ftp.Credentials = new NetworkCredential(ftpUser.Value, ftpPassword.Value);
            //Set keepalive property true to multiple use of same instance  
            ftp.KeepAlive = true;
            //use binary method to upload data
            ftp.UseBinary = true;
            //set action (upload or download
            ftp.Method = WebRequestMethods.Ftp.UploadFile;
            
            //open file in filesteam
            FileStream fs = File.OpenRead(localPath.Value + localFile.Value);
            byte[] buffer = new byte[fs.Length];
            //read the file in filestream
            fs.Read(buffer, 0, buffer.Length);
            //close the filesteam
            fs.Close();
            //now open ftpsteam
            Stream ftpstream = ftp.GetRequestStream();
            //write at ftp
            ftpstream.Write(buffer, 0, buffer.Length);
            //close the ftp stream
            ftpstream.Close();

        }
        catch (Exception ex)
        {
            //handle exceptions
            throw;
        }
        //       }


        // Put your code here
        return true;
    }
};

